package com.lanou3g.bookstore.book.dao;

import com.lanou3g.bookstore.book.domain.Book;
import com.lanou3g.bookstore.util.util.JDBCUtil;
import com.lanou3g.bookstore.util.util.MyResultSet;
import com.lanou3g.bookstore.util.util.MyStatement;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {

    //查询所有书
    public List<Book> findAll() {
        String sql = "select * from book where del = false";
        MyStatement statement = JDBCUtil.getStatement(sql);
        MyResultSet rs = statement.executeQuery();
        List<Book> books = new ArrayList<>();
        while (rs.next()) {
            Book book = new Book();
            book.setBname(rs.getString("bname"))
                    .setPrice(rs.getDouble("price"))
                    .setAuthor(rs.getString("author"))
                    .setImage(rs.getString("image"))
                    .setCid(rs.getString("cid"))
                    .setBid(rs.getString("bid"));
            books.add(book);
        }
        rs.close();
        statement.close();
        return books;
    }

    //查询一类书
    public List<Book> findByCategory(int cid) {
        String sql = "select * from book where cid = ? and del = false";
        MyStatement statement = JDBCUtil.getStatement(sql);
        statement.setInt(1, cid);
        MyResultSet rs = statement.executeQuery();
        List<Book> books = new ArrayList<>();
        while (rs.next()) {
            Book book = new Book();
            book.setBname(rs.getString("bname"))
                    .setPrice(rs.getDouble("price"))
                    .setAuthor(rs.getString("author"))
                    .setImage(rs.getString("image"))
                    .setCid(rs.getString("cid"))
                    .setBid(rs.getString("bid"));
            books.add(book);
        }
        rs.close();
        statement.close();
        return books;
    }

    //查询单本书
    public Book load(String bid) {
        String sql = "select * from book where bid = ? and del = false";
        MyStatement statement = JDBCUtil.getStatement(sql);
        statement.setString(1, bid);
        MyResultSet rs = statement.executeQuery();
        rs.next();
        Book book = new Book();
        book.setBname(rs.getString("bname"))
                .setPrice(rs.getDouble("price"))
                .setAuthor(rs.getString("author"))
                .setImage(rs.getString("image"))
                .setCid(rs.getString("cid"))
                .setBid(rs.getString("bid"));
        return book;
    }

    //添加书籍
    public void add(Book book) {
        try {
            String sql = "insert into book(bid, bname, price, author, image, cid) values (?, ?, ?, ?, ?, ?)";
            MyStatement statement = JDBCUtil.getStatement(sql);
            statement.setString(1, book.getBid());
            statement.setString(2, book.getBname());
            statement.setDouble(3, book.getPrice());
            statement.setString(4, book.getAuthor());
            statement.setString(5, book.getImage());
            statement.setString(6, book.getCid());
            statement.executeUpdate();
            statement.close();
        } catch (Exception e) {
            try {
                JDBCUtil.getConn().rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

    }

//    //查询书籍最大bid
//    public Integer findMaxBid() {
//        String sql = "select bid from book where del = false order by bid desc limit 0,1";
//        MyStatement statement = JDBCUtil.getStatement(sql);
//        MyResultSet rs = statement.executeQuery();
//        rs.next();
//        int count = rs.getInt("bid");
//        rs.close();
//        statement.close();
//        return count;
//    }

    //删除书
    public void del(String bid) {
        String sql = "update book set del = true where bid = ?";
        MyStatement statement = JDBCUtil.getStatement(sql);
        statement.setString(1, bid);
        statement.executeUpdate();
        statement.close();
        JDBCUtil.closeConn();
    }

    //修改书
    public void mod(Book book) {
        String sql = "update book set bname = ?, price = ?," +
                " author = ?, cid = ? where bid = ?";
        MyStatement statement = JDBCUtil.getStatement(sql);
        statement.setString(1, book.getBname());
        statement.setDouble(2, book.getPrice());
        statement.setString(3, book.getAuthor());
        statement.setString(4, book.getCid());
        statement.setString(5, book.getBid());
        statement.executeUpdate();
        statement.close();
    }

}
